﻿<%@ WebHandler Language="C#" Class="ApsPlanInfoPreSpotCheckHandler" %>

/* Description:预排产工序点检后端接口
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan 
 * create Date:2024.12.17
 */

using System;
using System.Web;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BusinessRule.DTO;
using DatabaseAccess.DBUtility;
using System.Web.SessionState;
using Newtonsoft.Json;
using System.Collections;
using System.IO;
using System.Configuration;

public class ApsPlanInfoPreSpotCheckHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo = new CurrentUserInfo();
    string exportPath = ConfigurationManager.AppSettings["ExportPath"]; //导出路径
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "getUserInfo":
                    getUserInfo(context);
                    break;
                case "getData":
                    getData(context);
                    break;
                case "getDetails":
                    getDetails(context);//查看明细
                    break;
                case "setAllSet":
                    setAllSet(context);//一键齐套
                    break;
                case "spotcheck":
                    spotcheck(context);//点检确认
                    break;
                case "update":
                    update(context);//更新
                    break;
                case "exportExcel":
                    exportExcel(context);//导出
                    break;
                case"keyspotcheck"://一键点检
                    keyspotcheck(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(result));
        }
    }

    //生成查询sql
    string getQuerySql(HttpContext context)
    {
        string sql = @"
SELECT '排产' type,'未点检' status,aps.apsplandetailid apsplandetailpreid,NULL id,f.factoryname,
       cci.mfgordername,cci.productname,cci.containername,aps.workflowstepname,cci.familylistnames,
       decode(cci.containertype,2,'批量加工',3,'批量带子序号',7,'单件加工') containertype,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty,aps.starttime estallsetdate,
       aps.starttime,aps.endtime,
       NULL allset,NULL materialset, 
       NULL toolset,NULL auxset,NULL workflowset,NULL ncprogramset,NULL checkpointset,NULL resourceset,
       NULL shipstepset,NULL qualityset,NULL lastchangeemp,NULL lastchangedate,NULL marktype,
       aps.containerid,aps.workflowstepid
FROM apsplandetailpre aps
INNER JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN ApsPlanInfoPreSpotCheck sc ON sc.apsplandetailpreid = aps.apsplandetailid AND aps.isuse = 0
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE sc.id IS NULL AND aps.isuse = 0 AND cci.isinstore = 0";

        //订单号
        if (context.Request["mfgordername"] != null && context.Request["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername LIKE '%{0}%'", context.Request["mfgordername"].ToString());
        }

        //零件号
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }

        //批次号
        if (context.Request["containername"] != null && context.Request["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", context.Request["containername"].ToString());
        }

        //工序
        if (context.Request["workflowstepname"] != null && context.Request["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND aps.workflowstepname LIKE '%{0}%'", context.Request["workflowstepname"].ToString());
        }

        //分厂
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", context.Request["factoryid"].ToString());
        }

        //点检状态
        if (context.Request["ddlCheck"] != null)
        {
            string status = context.Request["ddlCheck"].ToString();
            if (status == "已点检")
            {
                sql += " AND 1=0";
            }
            else
            {
                sql += " AND 1=1";
            }
        }
        //计划开始/结束时间
        if (!string.IsNullOrWhiteSpace(context.Request["planStartDate"]))
        {
            sql += " and aps.starttime>=to_date('" + context.Request["planStartDate"].ToString() + "','yyyy-MM-dd')";
        }
        if (!string.IsNullOrWhiteSpace(context.Request["planEndDate"]))
        {
            sql += " and aps.endtime<=to_date('" + context.Request["planEndDate"].ToString() + "','yyyy-MM-dd')";
        }
        sql += @"

UNION ALL

SELECT '点检' type,'已点检' status,sc.apsplandetailpreid,sc.id,f.factoryname,
       cci.mfgordername,cci.productname,cci.containername,aps.workflowstepname,cci.familylistnames,
       decode(cci.containertype,2,'批量加工',3,'批量带子序号',7,'单件加工') containertype,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty,NVL(sc.estallsetdate,aps.starttime) estallsetdate,
       aps.starttime,aps.endtime,
       sc.allset,sc.materialset,sc.toolset,sc.auxset,sc.workflowset,sc.ncprogramset,sc.checkpointset,sc.resourceset,sc.shipstepset,sc.qualityset,
       e.fullname lastchangeemp,sc.lastchangedate,sc.marktype,
       aps.containerid,aps.workflowstepid
FROM ApsPlanInfoPreSpotCheck sc
INNER JOIN apsplandetailpre aps ON sc.apsplandetailpreid = aps.apsplandetailid AND aps.isuse = 0
INNER JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN employee e ON e.employeeid = sc.lastchangeemp
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE cci.isinstore = 0
";
        //订单号
        if (context.Request["mfgordername"] != null && context.Request["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername LIKE '%{0}%'", context.Request["mfgordername"].ToString());
        }

        //零件号
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }

        //批次号
        if (context.Request["containername"] != null && context.Request["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", context.Request["containername"].ToString());
        }

        //工序
        if (context.Request["workflowstepname"] != null && context.Request["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND aps.workflowstepname LIKE '%{0}%'", context.Request["workflowstepname"].ToString());
        }

        //是否齐套
        if (context.Request["allset"] != null && context.Request["allset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.allset,0) = '{0}'", context.Request["allset"].ToString());
        }

        //物料齐套
        if (context.Request["materialset"] != null && context.Request["materialset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.materialset,0) = '{0}'", context.Request["materialset"].ToString());
        }

        //工装齐套
        if (context.Request["toolset"] != null && context.Request["toolset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.toolset,0) = '{0}'", context.Request["toolset"].ToString());
        }

        //辅材齐套
        if (context.Request["auxset"] != null && context.Request["auxset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.auxset,0) = '{0}'", context.Request["auxset"].ToString());
        }

        //工艺齐套
        if (context.Request["workflowset"] != null && context.Request["workflowset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.workflowset,0) = '{0}'", context.Request["workflowset"].ToString());
        }

        //NC程序齐套
        if (context.Request["ncprogramset"] != null && context.Request["ncprogramset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.ncprogramset,0) = '{0}'", context.Request["ncprogramset"].ToString());
        }

        //特殊过程齐套
        if (context.Request["checkpointset"] != null && context.Request["checkpointset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.checkpointset,0) = '{0}'", context.Request["checkpointset"].ToString());
        }

        //设备齐套
        if (context.Request["resourceset"] != null && context.Request["resourceset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.resourceset,0) = '{0}'", context.Request["resourceset"].ToString());
        }

        //实物齐套
        if (context.Request["shipstepset"] != null && context.Request["shipstepset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.shipstepset,0) = '{0}'", context.Request["shipstepset"].ToString());
        }

        //质量齐套
        if (context.Request["qualityset"] != null && context.Request["qualityset"].ToString() != "")
        {
            sql += string.Format(" AND NVL(sc.qualityset,0) = '{0}'", context.Request["qualityset"].ToString());
        }

        //分厂
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", context.Request["factoryid"].ToString());
        }

        //点检状态
        if (context.Request["ddlCheck"] != null)
        {
            string status = context.Request["ddlCheck"].ToString();
            if (status == "已点检")
            {
                sql += " AND 1=1";
            }
            else
            {
                sql += " AND 1=0";
            }
        }
        //计划开始/结束时间
        if (!string.IsNullOrWhiteSpace(context.Request["planStartDate"]))
        {
            sql += " and aps.starttime>=to_date('" + context.Request["planStartDate"].ToString() + "','yyyy-MM-dd')";
        }
        if (!string.IsNullOrWhiteSpace(context.Request["planEndDate"]))
        {
            sql += " and aps.endtime<=to_date('" + context.Request["planEndDate"].ToString() + "','yyyy-MM-dd')";
        }
        return sql;
    }

    void getUserInfo(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", ReturnData = m_UserInfo }));
        }
    }

    void getData(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            string sql = getQuerySql(context);
            uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
            paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
            paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
            paraPage.strSQL = sql;
            uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
            foreach (DataColumn col in result.DBTable.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    //查询明细
    void getDetails(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
            return;
        }
        else
        {
            string containerid = context.Request.Form["containerid"].ToString();
            string workflowstepid = context.Request.Form["workflowstepid"].ToString();
            string mfgordername = context.Request.Form["mfgordername"].ToString();

            DataSet ds = new DataSet();
            //string sql = @"
            //SELECT mfg.mfgordername,mfg.qty,DECODE(mfg.iscompproduct,'1','是','否') iscompproduct,mfg.receivedate,
            //       DECODE(mfg.sendstart,'1','是','否') sendstart,mfg.sendstartdate,e.fullname sendstartemp
            //FROM mfgorder mfg
            //LEFT JOIN employee e ON e.employeeid = mfg.sendstartemp
            //WHERE mfg.mfgordername = '{0}'";
            //DataTable dtMfg = DatabaseHelper.Query_ReturnDatTable(string.Format(sql, mfgordername));
            //foreach (DataColumn col in dtMfg.Columns)
            //{
            //    col.ColumnName = col.ColumnName.ToLower();
            //}
            //dtMfg.TableName = "dtMfg";
            //ds.Tables.Add(dtMfg);

            //查询工装
            string toolSql = @"
SELECT ws.workflowid,ws.workflowstepname,
       t.toolname,t.description,t.resourcetypename,t.tooltype,
       NVL(t.qty,0) qty,NVL(tg.menge,0) labstall,
       CASE WHEN NVL(t.qty,0) >= NVL(tg.menge,0) THEN NVL(t.qty,0) - NVL(tg.menge,0) ELSE 0 END lackqty,
       CASE WHEN NVL(tg.menge,0)>=NVL(t.qty,0) THEN '是' ELSE '否' END iscomp  
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws  ON ws.workflowid =w.workflowid 
LEFT JOIN (
   SELECT SUM(qty) qty,ct.toolname,ct.description,drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
   FROM (
      SELECT t.qty, t.sequence,t.tools,t.workflowstepid 
      FROM workflowsteptools t
      WHERE t.workflowstepid='{0}'
      UNION
      SELECT t.qty, t.sequence,t.tools,sio.workflowstepid 
      FROM stepinfotools t
      LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
      LEFT JOIN workflowstep ws ON ws.workflowstepid=sio.workflowstepid 
      WHERE ws.workflowstepid='{0}'
   ) t 
   LEFT JOIN (
      SELECT cuttoolname toolname,DESCRIPTION,cuttoolid toolid,resourcetypeid,drawingno,'刀具' tooltype
      FROM  cuttool
      UNION ALL
      SELECT measuretoolname toolname,DESCRIPTION,measuretoolid toolid,resourcetypeid,drawingno,'量具' tooltype 
      FROM  measuretool
      UNION ALL
      SELECT specialtoolname toolname,DESCRIPTION,specialtoolid toolid,resourcetypeid,drawingno,'夹模具' tooltype
      FROM  specialtool
   ) ct  ON ct.toolid = t.tools
   LEFT JOIN resourcetype rt  ON rt.resourcetypeid = ct.resourcetypeid WHERE ct.toolid IS NOT NULL 
   GROUP BY ct.toolname,ct.description,ct.drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
) t ON t.workflowstepid=ws.workflowstepid
LEFT JOIN (
     SELECT SUM(tg.qty) menge,tg.toolname 
     FROM tool_stock_info tg 
     GROUP BY tg.toolname
) tg ON tg.toolname=t.toolname
WHERE ws.workflowstepid='{0}' AND t.toolname IS NOT NULL";
            toolSql = string.Format(toolSql, workflowstepid);
            DataTable dtTools = DatabaseHelper.Query_ReturnDatTable(toolSql);
            foreach (DataColumn col in dtTools.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            dtTools.TableName = "dtTools";
            ds.Tables.Add(dtTools);

            //查询辅材
            string auxSql = @"
SELECT ws.workflowstepid,ws.workflowstepname,       
       t.auxmaterialname,t.description,t.qty,t.labstall,t.iscomp,
       CASE WHEN NVL(t.qty,0) >= NVL(t.labstall,0) THEN NVL(t.qty,0) - NVL(t.labstall,0) ELSE 0 END lackqty
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws  ON ws.workflowid =w.workflowid 
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN (
    SELECT am.auxmaterialname,am.description,SUM(qty)qty,t.workflowstepid,NVL(tg.menge,0) labstall,SUM(qty)-NVL(tg.menge,0) diffqty,
           CASE WHEN NVL(tg.menge,0)>=NVL(SUM(qty),0) THEN '是' ELSE '否' END iscomp
    FROM (
         SELECT t.qty, t.sequence,t.tools,t.workflowstepid 
         FROM workflowsteptools t
         LEFT JOIN workflowstep ws ON ws.workflowstepid=t.workflowstepid 
         WHERE ws.workflowstepid='{0}'
         UNION 
         SELECT t.qty, t.sequence,t.tools,sio.workflowstepid FROM stepinfotools t
         LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
         LEFT JOIN workflowstep ws ON ws.workflowstepid=sio.workflowstepid
         WHERE ws.workflowstepid='{0}'
    ) t 
    LEFT JOIN auxmaterial am ON am.auxmaterialid=t.tools 
    LEFT JOIN(
         SELECT SUM(tg.qty) menge,tg.auxmaterialname 
         FROM auxmaterial_stock_info tg 
         GROUP BY tg.auxmaterialname
    ) tg ON tg.auxmaterialname=am.auxmaterialname
    WHERE am.auxmaterialid IS NOT NULL
    GROUP BY am.auxmaterialname,t.workflowstepid,am.description,tg.menge
)t ON t.workflowstepid=ws.workflowstepid 
WHERE ws.workflowstepid='{0}' AND t.auxmaterialname IS NOT NULL";
            auxSql = string.Format(auxSql, workflowstepid);
            DataTable dtAux = DatabaseHelper.Query_ReturnDatTable(auxSql);
            foreach (DataColumn col in dtAux.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            dtAux.TableName = "dtAux";
            ds.Tables.Add(dtAux);

            //查询特殊过程
            string checkpointSql = @"
SELECT wb.workflowname||':'||w. workflowrevision workflowinfo,
       w.workflowid,ws.workflowstepid,ws.workflowstepname,s.specno,
       sr.specialcoursespecreportno,to_char(sr.indate,'yyyy-MM-dd hh24:mi:ss') indate,
       CASE WHEN sr.indate IS NOT NULL AND sr.indate>SYSDATE THEN '是' ELSE '否' END iscomp
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowid =w.workflowid 
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid) 
--LEFT JOIN specialcoursespecmapp sc ON sc.workflowstepid =ws.workflowstepid 
--LEFT JOIN specialcoursespecreport sr ON sr.specialcoursespecreportid =sc.specialcoursespecreportid
LEFT JOIN (
		select sc.workflowstepid,sr.specialcoursespecreportno,sr.indate from specialcoursespecmapp sc 
		left join specialcoursespecreport sr on sr.specialcoursespecreportid =sc.specialcoursespecreportid
		where sr.indate is not null and sc.workflowstepid='{0}'
		union all 
		select sm.workflowstepid,nvl(sc.specialcoursespecreportno,sr.specialcoursespecreportno) specialcoursespecreportno,sr.indate from specialcoursespecmapp sm 
        left join specialcoursespecreport sr on sr.specialcoursespecreportid =sm.specialcoursespecreportid
        left join ( select s.code,sk.specialcoursespecreportno,add_months(sk.indate,regexp_substr(sk.period , '\d+')*12) indate from specialcoursespeccontent s 
               inner join specialcoursespecreportbank sk on sk.specialcoursespecreportbankid =s.specialcoursespecreportbankid) sc on instr(sr.specialcoursespecreportno,sc.code)>0
        where sc.indate is null and sm.workflowstepid='{0}'
		 ) sr ON sr.workflowstepid =ws.workflowstepid 
WHERE ws.workflowstepid='{0}' AND sr.specialcoursespecreportno IS NOT NULL AND NVL(s.isspotcheck,0)=1";
            checkpointSql = string.Format(checkpointSql, workflowstepid);
            DataTable dtCheckPoint = DatabaseHelper.Query_ReturnDatTable(checkpointSql);
            foreach (DataColumn col in dtCheckPoint.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            dtCheckPoint.TableName = "dtCheckPoint";
            ds.Tables.Add(dtCheckPoint);

            //数控程序
            string ncSql = @"
SELECT ws.workflowstepid,ws.workflowstepname,s.specid,s.specno,DECODE(s.ncnumber,'1','是','否') isnc,s.nccode,
       CASE WHEN NVL(s.ncnumber,'0') = '1' AND s.nccode IS NULL THEN '否' ELSE '是' END iscomp
FROM workflowstep ws
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
WHERE ws.workflowstepid = '{0}'";
            ncSql = string.Format(ncSql, workflowstepid);
            DataTable dtNcProgram = DatabaseHelper.Query_ReturnDatTable(ncSql);
            foreach (DataColumn col in dtNcProgram.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            dtNcProgram.TableName = "dtNcProgram";
            ds.Tables.Add(dtNcProgram);

            //实物
            //string shipStepSql = @"
            //SELECT aps.containerid,cci.containername,aps.workflowstepid,aps.workflowstepname,aps.endtime,csv.recivedate,'否' iscomp
            //FROM apsplandetail aps
            //LEFT JOIN containershiprecive csv ON csv.containerid = aps.containerid AND csv.workflowstepid = aps.workflowstepid
            //LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
            //WHERE aps.containerid = '{0}' AND aps.workflowstepid = '{1}'
            //      AND ((csv.recivedate IS NOT NULL AND csv.recivedate > aps.endtime) OR (csv.recivedate IS NULL AND SYSDATE > aps.endtime))";
            //shipStepSql = string.Format(shipStepSql, containerid, workflowstepid);
            //DataTable dtShipStep = DatabaseHelper.Query_ReturnDatTable(shipStepSql);
            //foreach (DataColumn col in dtShipStep.Columns)
            //{
            //    col.ColumnName = col.ColumnName.ToLower();
            //}
            //dtShipStep.TableName = "dtShipStep";
            //ds.Tables.Add(dtShipStep);
            //8.设备齐套
            string resourceSql = @"select  rf.resourcename,rf.description,'设备状态' erpstatus ,case when '设备状态' ='可用' then '是' else '否' end iscomp
                    from workflowstep ws
                    left join specbase sb on sb.specbaseid = ws.specbaseid
                    left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid)
                    left join specresourceinfo sr on sr.specid =s.specid 
                    left join resourcedef rf on rf.resourceid =sr.resourceid 
                    --left join  ---erp设备台账表（设备状态【正常可用：是；else：否】）
                    where ws.workflowstepid = '{0}' ";
            DataTable dtResource = DatabaseHelper.Query_ReturnDatTable(string.Format(resourceSql, workflowstepid));
            foreach (DataColumn col in dtResource.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            dtResource.TableName = "dtResource";
            ds.Tables.Add(dtResource);
            //物料
            string materialSql = @"SELECT pb.PRODUCTNAME,p.DESCRIPTION,pm.QTYREQUIRED,'0' QTY, '是' iscomp
                        FROM  productmateriallistitem pm 
                        LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid
                        LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid)
                        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                        LEFT JOIN SPECBASE sb ON sb.SPECBASEID =pm.SPECBASEID 
                        LEFT JOIN SPEC S ON  S.SPECID=NVL(SB.REVOFRCDID, PM.SPECID)
                        ---LEFT JOIN xxxx x on       
                        WHERE s.specid IN (SELECT nvl(s2.specid,ws.SPECID) specid
                        FROM WORKFLOWSTEP ws
                        LEFT JOIN SPEC s2 ON s2.SPECBASEID =ws.SPECBASEID 
                        WHERE ws.WORKFLOWSTEPID ='{0}')";
           DataTable dtMaterial = DatabaseHelper.Query_ReturnDatTable(string.Format(materialSql, workflowstepid));
           foreach (DataColumn col in dtMaterial.Columns)
           {
               col.ColumnName = col.ColumnName.ToLower();
           }
           dtMaterial.TableName = "dtMfg";
           ds.Tables.Add(dtMaterial);
            
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { DBDataSet = ds, Result = true, Message = "请求成功", DBTable = null }));
        }
    }

    //一键齐套
    void setAllSet(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
            return;
        }
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string updateSql = @"
UPDATE ApsPlanInfoPreSpotCheck t 
SET t.allset = '1',t.materialset = '1',t.toolset = '1',t.auxset = '1',t.checkpointset = '1',
    t.ncprogramset = '1',t.qualityset = '1',t.resourceset = '1',t.shipstepset = '1',t.workflowset = '1',
    t.lastchangedate = sysdate,t.lastchangeemp = '{1}'
WHERE t.id = '{0}'";
            updateSql = string.Format(updateSql, dr["id"].ToString(), m_UserInfo.EmployeeID);
            sqlList.Add(updateSql);
        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = isSuccess, Message = "一键齐套成功", DBTable = null }));
    }

    //点检确认
    void spotcheck(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
            return;
        }
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        var ddlType = context.Request.Form["ddlType"].ToString();//ddl类型（insert、update）
        var result=spotcheckDeal(dtPostData, ddlType);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = result, Message = result ? "点检确认成功" : "点检确认失败", DBTable = null }));
    }
    Boolean spotcheckDeal(DataTable dtPostData, string ddlType)
    {
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string marktype = "1";//点检确认时，标记方式都为1：（自动）
            string allset, materialset, toolset, auxset, checkpointset, ncprogramset, qualityset, resourceset, shipstepset, workflowset;

            string sql = @"SELECT mfg.iscompproduct FROM mfgorder mfg WHERE NVL(mfg.iscompproduct,'0') = '1' AND mfg.mfgordername = '{0}'";
            DataTable dtMfg = DatabaseHelper.Query_ReturnDatTable(string.Format(sql, dr["mfgordername"].ToString()));
            //1.物料齐套
            materialset = dtMfg.Rows.Count > 0 ? "1" : "2";
            sql = @"SELECT pb.PRODUCTNAME,p.DESCRIPTION,pm.QTYREQUIRED 
                        FROM  productmateriallistitem pm 
                        LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid
                        LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid)
                        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                        LEFT JOIN SPECBASE sb ON sb.SPECBASEID =pm.SPECBASEID 
                        LEFT JOIN SPEC S ON  S.SPECID=NVL(SB.REVOFRCDID, PM.SPECID)
                        {1}--关联ERP库存表，判断数量是否大于需求数     
                        WHERE s.specid IN (  SELECT nvl(s2.specid,ws.SPECID) specid
                        FROM WORKFLOWSTEP ws
                        LEFT JOIN SPEC s2 ON s2.SPECBASEID =ws.SPECBASEID 
                        WHERE ws.WORKFLOWSTEPID ='{0}') {2}-- and x.QTY<pm.QTYREQUIRED";
            dtMfg = DatabaseHelper.Query_ReturnDatTable(string.Format(sql, dr["workflowstepid"].ToString(),"",""));
            //查询工序是否存在物料清单：不存在直接默认是齐套
            if (dtMfg.Rows.Count > 0) { //
                dtMfg = DatabaseHelper.Query_ReturnDatTable(string.Format(sql, dr["workflowstepid"].ToString(), "", ""));
                materialset = dtMfg.Rows.Count > 0 ? "1" : "2";
            } else { materialset = "1"; }
            
            //查询工装
            string toolSql = @"
SELECT ws.workflowid,ws.workflowstepname,
       t.toolname,t.description,t.resourcetypename,
       NVL(t.qty,0) qty, NVL(tg.menge,0) labstall,
       CASE WHEN NVL(tg.menge,0)>=NVL(t.qty,0) THEN '是' ELSE '否' END iscomp  
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws  ON ws.workflowid =w.workflowid 
LEFT JOIN (
   SELECT SUM(qty) qty,ct.toolname,ct.description,drawingno,rt.resourcetypename,t.workflowstepid 
   FROM (
      SELECT t.qty, t.sequence,t.tools,t.workflowstepid 
      FROM workflowsteptools t
      WHERE t.workflowstepid='{0}'
      UNION
      SELECT t.qty, t.sequence,t.tools,sio.workflowstepid 
      FROM stepinfotools t
      LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
      LEFT JOIN workflowstep ws ON ws.workflowstepid=sio.workflowstepid 
      WHERE ws.workflowstepid='{0}'
   ) t 
   LEFT JOIN (
      SELECT cuttoolname toolname,DESCRIPTION,cuttoolid toolid,resourcetypeid,drawingno 
      FROM  cuttool
      UNION ALL
      SELECT measuretoolname toolname,DESCRIPTION,measuretoolid toolid,resourcetypeid,drawingno 
      FROM  measuretool
      UNION ALL
      SELECT specialtoolname toolname,DESCRIPTION,specialtoolid toolid,resourcetypeid,drawingno 
      FROM  specialtool
   ) ct  ON ct.toolid = t.tools
   LEFT JOIN resourcetype rt  ON rt.resourcetypeid = ct.resourcetypeid WHERE ct.toolid IS NOT NULL 
   GROUP BY ct.toolname,ct.description,ct.drawingno,rt.resourcetypename,t.workflowstepid
) t ON t.workflowstepid=ws.workflowstepid
LEFT JOIN (
     SELECT SUM(tg.qty) menge,tg.toolname 
     FROM tool_stock_info tg 
     GROUP BY tg.toolname
) tg ON tg.toolname=t.toolname
WHERE ws.workflowstepid='{0}' AND t.toolname IS NOT NULL";
            toolSql = string.Format(toolSql, dr["workflowstepid"].ToString());
            DataTable dtTools = DatabaseHelper.Query_ReturnDatTable(toolSql);
            DataView dvTools = dtTools.DefaultView;
            dvTools.RowFilter = "ISCOMP='否'";
            //2.工装齐套
            toolset = dvTools.ToTable().Rows.Count > 0 ? "2" : "1";

            //查询辅材
            string auxSql = @"
SELECT ws.workflowstepid,ws.workflowstepname,       
       t.auxmaterialname, t.description, t.qty, t.labstall, t.iscomp
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws  ON ws.workflowid =w.workflowid 
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN (
    SELECT am.auxmaterialname,am.description,SUM(qty)qty,t.workflowstepid,NVL(tg.menge,0) labstall,SUM(qty)-NVL(tg.menge,0) diffqty,
           CASE WHEN NVL(tg.menge,0)>=NVL(SUM(qty),0) THEN '是' ELSE '否' END iscomp
    FROM (
         SELECT t.qty, t.sequence,t.tools,t.workflowstepid 
         FROM workflowsteptools t
         LEFT JOIN workflowstep ws ON ws.workflowstepid=t.workflowstepid 
         WHERE ws.workflowstepid='{0}'
         UNION 
         SELECT t.qty, t.sequence,t.tools,sio.workflowstepid FROM stepinfotools t
         LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
         LEFT JOIN workflowstep ws ON ws.workflowstepid=sio.workflowstepid
         WHERE ws.workflowstepid='{0}'
    ) t 
    LEFT JOIN auxmaterial am ON am.auxmaterialid=t.tools 
    LEFT JOIN(
         SELECT SUM(tg.qty) menge,tg.auxmaterialname 
         FROM auxmaterial_stock_info tg 
         GROUP BY tg.auxmaterialname
    ) tg ON tg.auxmaterialname=am.auxmaterialname
    WHERE am.auxmaterialid IS NOT NULL
    GROUP BY am.auxmaterialname,t.workflowstepid,am.description,tg.menge
)t ON t.workflowstepid=ws.workflowstepid 
WHERE ws.workflowstepid='{0}' AND t.auxmaterialname IS NOT NULL";

            auxSql = string.Format(auxSql, dr["workflowstepid"].ToString());
            DataTable dtAux = DatabaseHelper.Query_ReturnDatTable(auxSql);
            DataView dvAux = dtAux.DefaultView;
            dvAux.RowFilter = "ISCOMP='否'";
            //3.辅材齐套
            auxset = dvAux.ToTable().Rows.Count > 0 ? "2" : "1";

            //查询特殊过程
            string checkpointSql = @"
SELECT wb.workflowname||':'||w. workflowrevision workflowinfo,
       w.workflowid,ws.workflowstepid,ws.workflowstepname,
       sr.specialcoursespecreportno,to_char(sr.indate,'yyyy-MM-dd hh24:mi:ss') indate,
       CASE WHEN sr.indate IS NOT NULL AND sr.indate>SYSDATE THEN '是' ELSE '否' END iscomp
FROM  workflowbase wb 
LEFT JOIN workflow w ON w.workflowbaseid =wb.workflowbaseid
LEFT JOIN workflowstep ws  ON ws.workflowid =w.workflowid 
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN apsplandetail apd ON apd.workflowstepid=ws.workflowstepid     
--LEFT JOIN specialcoursespecmapp sc ON sc.workflowstepid =ws.workflowstepid 
--LEFT JOIN specialcoursespecreport sr ON sr.specialcoursespecreportid =sc.specialcoursespecreportid
LEFT JOIN (
		select sc.workflowstepid,sr.specialcoursespecreportno,sr.indate from specialcoursespecmapp sc 
		left join specialcoursespecreport sr on sr.specialcoursespecreportid =sc.specialcoursespecreportid
		where sr.indate is not null and sc.workflowstepid='{0}'
		union all 
		select sm.workflowstepid,nvl(sc.specialcoursespecreportno,sr.specialcoursespecreportno) specialcoursespecreportno,sr.indate from specialcoursespecmapp sm 
        left join specialcoursespecreport sr on sr.specialcoursespecreportid =sm.specialcoursespecreportid
        left join ( select s.code,sk.specialcoursespecreportno,add_months(sk.indate,regexp_substr(sk.period , '\d+')*12) indate from specialcoursespeccontent s 
               inner join specialcoursespecreportbank sk on sk.specialcoursespecreportbankid =s.specialcoursespecreportbankid) sc on instr(sr.specialcoursespecreportno,sc.code)>0
        where sc.indate is null and sm.workflowstepid='{0}'
		 ) sr ON sr.workflowstepid =ws.workflowstepid 
WHERE NVL(s.isspotcheck,0)=1 AND ws.workflowstepid='{0}' AND sr.specialcoursespecreportno IS NOT NULL
";
            checkpointSql = string.Format(checkpointSql, dr["workflowstepid"].ToString());
            DataTable dtCheckPoint = DatabaseHelper.Query_ReturnDatTable(checkpointSql);
            DataView dvCheckPoint = dtCheckPoint.DefaultView;
            dvCheckPoint.RowFilter = "ISCOMP='否'";
            //4.特殊过程齐套
            checkpointset = dvCheckPoint.ToTable().Rows.Count > 0 ? "2" : "1";

            //数控程序
            string ncSql = @"
SELECT ws.workflowstepid,ws.workflowstepname,s.specid,NVL(s.ncnumber,0) isnc,s.nccode
FROM workflowstep ws
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
WHERE ws.workflowstepid = '{0}' AND NVL(s.ncnumber,0) = 1 AND s.nccode IS NULL";

            ncSql = string.Format(ncSql, dr["workflowstepid"].ToString());
            DataTable dtNc = DatabaseHelper.Query_ReturnDatTable(ncSql);
            //查询NC明细
            string ncDSql = @" select n.nc_no,n.nc_name,n.material_mark,n.isthree,n.istypical,n.control_type,nvl(nd.equip_code,nd.filename) eqpiporfilename,nd.filepath 
            from workflowstep ws
            inner join workflow w on w.workflowid=ws.workflowid
            inner join ncprogram n on n.process_no =w.process_no and n.process_rev =w.workflowrevision 
            left join ncprogramdetaile nd on nd.parentid =n.id 
            where ws.workflowstepid ='{0}'";
            DataTable dtDNc = DatabaseHelper.Query_ReturnDatTable(string.Format(ncDSql, dr["workflowstepid"].ToString()));
            //5.数控程序齐套
            ncprogramset = (dtNc.Rows.Count > 0 && dtDNc.Rows.Count==0) ? "2" : "1";

            string shipStepSql = @"
SELECT aps.containerid,cci.containername,aps.workflowstepid,aps.workflowstepname,aps.endtime,csv.recivedate
FROM apsplandetail aps
LEFT JOIN containershiprecive csv ON csv.containerid = aps.containerid AND csv.workflowstepid = aps.workflowstepid
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
WHERE aps.containerid = '{0}' AND aps.workflowstepid = '{1}'
      AND ((csv.recivedate IS NOT NULL AND csv.recivedate > aps.endtime) OR (csv.recivedate IS NULL AND SYSDATE > aps.endtime))";
            shipStepSql = string.Format(shipStepSql, dr["containerid"].ToString(), dr["workflowstepid"].ToString());
            DataTable dtShipStep = DatabaseHelper.Query_ReturnDatTable(shipStepSql);
            //6.实物齐套
            shipstepset = "1"; //dtShipStep.Rows.Count > 0 ? "2" : "1";
            //8.设备齐套
            string resourceSql = @"SELECT  CASE WHEN '设备状态' ='可用' THEN '是' ELSE '否' END iscomp
                    FROM workflowstep ws
                    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                    LEFT JOIN SPECRESOURCEINFO sr ON sr.SPECID =s.SPECID 
                    LEFT JOIN RESOURCEDEF rf ON rf.RESOURCEID =sr.RESOURCEID 
                    --LEFT JOIN  ---ERP设备台账表（设备状态【正常可用：是；else：否】）
                    WHERE ws.workflowstepid = '{0}' ";
            DataTable dtResource = DatabaseHelper.Query_ReturnDatTable(string.Format(resourceSql, dr["workflowstepid"].ToString()));
            DataView dvResource = dtResource.DefaultView;
            dvResource.RowFilter = "ISCOMP='否'";
            //8.设备齐套
            resourceset = "1"; //dvResource.ToTable().Rows.Count > 0 ? "2" : "1";

            string otherSql = "SELECT t.id,t.qualityset,t.workflowset,t.resourceset FROM ApsPlanInfoPreSpotCheck t WHERE t.id='{0}'";
            otherSql = string.Format(otherSql, dr["id"].ToString());
            DataTable dtOther = DatabaseHelper.Query_ReturnDatTable(otherSql);
            if (dtOther.Rows.Count > 0)
            {
                qualityset = "1"; //dtOther.Rows[0]["qualityset"].ToString();//7.质量齐套
                //resourceset = dtOther.Rows[0]["resourceset"].ToString();//8.设备齐套
                workflowset = dtOther.Rows[0]["workflowset"].ToString();//9.工艺齐套
            }
            else
            {
                qualityset = "1";
                //resourceset = "0";
                workflowset = "1";
            }

            string concat = string.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}", materialset, toolset, auxset, checkpointset, ncprogramset, qualityset, resourceset, shipstepset, workflowset);
            //10.是否齐套
            if (concat == "111111111")
            {
                allset = "1";
            }
            else
            {
                allset = "2";
            }

            if (ddlType == "insert")
            {
                string insertSql = @"
INSERT INTO ApsPlanInfoPreSpotCheck(
id,apsplandetailpreid,allset,materialset,toolset,
auxset,checkpointset,marktype,ncprogramset,qualityset,
resourceset,shipstepset,workflowset,createemp,
estallsetdate,createdate)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',
to_date('{14}','yyyy/mm/dd hh24:mi:ss'),sysdate)";
                insertSql = string.Format(insertSql,
                                          Guid.NewGuid().ToString(),//唯一id
                                          dr["apsplandetailpreid"].ToString(),//预排产id
                                          allset,//是否齐套
                                          materialset,
                                          toolset,
                                          auxset,
                                          checkpointset,
                                          marktype,
                                          ncprogramset,
                                          qualityset,
                                          resourceset,
                                          shipstepset,
                                          workflowset,
                                          m_UserInfo.EmployeeID,
                                          dr["estallsetdate"].ToString()//预齐套时间
                                         );
                sqlList.Add(insertSql);
            }
            else
            {
                string updateSql = @"
UPDATE ApsPlanInfoPreSpotCheck t SET t.allset = '{1}',t.materialset = '{2}',t.toolset = '{3}',t.auxset = '{4}',t.checkpointset = '{5}',
t.ncprogramset = '{6}',t.qualityset = '{7}',t.resourceset = '{8}',t.shipstepset = '{9}',t.workflowset = '{10}',
t.lastchangedate = sysdate,t.lastchangeemp = '{11}'
WHERE t.id = '{0}'";
                updateSql = string.Format(updateSql,
                                          dr["id"].ToString(),
                                          allset,//是否齐套
                                          materialset,
                                          toolset,
                                          auxset,
                                          checkpointset,
                                          ncprogramset,
                                          qualityset,
                                          resourceset,
                                          shipstepset,
                                          workflowset,
                                          m_UserInfo.EmployeeID//更新人
                                         );
                sqlList.Add(updateSql);
            }
        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        //context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "点检确认成功", DBTable = null }));
        return isSuccess;
    }

    void update(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
            return;
        }
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());

        string materialset = context.Request.Form["materialset"].ToString();
        string toolset = context.Request.Form["toolset"].ToString();
        string auxset = context.Request.Form["auxset"].ToString();
        string workflowset = context.Request.Form["workflowset"].ToString();
        string ncprogramset = context.Request.Form["ncprogramset"].ToString();
        string checkpointset = context.Request.Form["checkpointset"].ToString();
        string resourceset = context.Request.Form["resourceset"].ToString();
        string shipstepset = context.Request.Form["shipstepset"].ToString();
        string qualityset = context.Request.Form["qualityset"].ToString();
        string type = context.Request.Form["type"].ToString();
        string estallsetdate = context.Request.Form["estallsetdate"].ToString();

        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string DBmaterialset = dr["materialset"].ToString();
            string DBtoolset = dr["toolset"].ToString();
            string DBauxset = dr["auxset"].ToString();
            string DBcheckpointset = dr["checkpointset"].ToString();
            string DBncprogramset = dr["ncprogramset"].ToString();
            string DBshipstepset = dr["shipstepset"].ToString();
            string DBresourceset = dr["resourceset"].ToString();
            string DBqualityset = dr["qualityset"].ToString();
            string DBworkflowset = dr["workflowset"].ToString();

            if (type == "update")
            {
                string allset = "0";
                string concat = string.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}",
                                               materialset,
                                               toolset,
                                               auxset,
                                               checkpointset,
                                               ncprogramset,
                                               shipstepset,
                                               DBresourceset,
                                               DBqualityset,
                                               DBworkflowset);
                if (concat == "111111111")
                {
                    allset = "1";
                }
                else if (concat.Contains("2")) { allset = "2"; }
                if(string.IsNullOrWhiteSpace(materialset)||string.IsNullOrWhiteSpace(toolset)||string.IsNullOrWhiteSpace(auxset)||
                   string.IsNullOrWhiteSpace(checkpointset)||string.IsNullOrWhiteSpace(ncprogramset)||string.IsNullOrWhiteSpace(shipstepset)||
                   string.IsNullOrWhiteSpace(DBresourceset)||string.IsNullOrWhiteSpace(DBqualityset)||string.IsNullOrWhiteSpace(DBworkflowset))
                {
                    allset = "2";
                }
                
                if (estallsetdate != "")
                {
                    string updateSql = @"
UPDATE ApsPlanInfoPreSpotCheck t 
SET t.materialset = '{1}',t.toolset = '{2}',t.auxset = '{3}',t.checkpointset = '{4}',t.ncprogramset = '{5}',t.shipstepset = '{6}',
    t.lastchangedate = sysdate,t.lastchangeemp = '{7}',t.allset = '{8}',t.marktype = '2',t.estallsetdate = to_date('{9}','yyyy/mm/dd hh24:mi:ss')
WHERE t.id = '{0}'";
                    updateSql = string.Format(updateSql,
                        dr["id"].ToString(),
                        materialset,
                        toolset,
                        auxset,
                        checkpointset,
                        ncprogramset,
                        shipstepset,
                        m_UserInfo.EmployeeID,
                        allset,
                        estallsetdate);
                    sqlList.Add(updateSql);
                }
                else
                {
                    string updateSql = @"
UPDATE ApsPlanInfoPreSpotCheck t 
SET t.materialset = '{1}',t.toolset = '{2}',t.auxset = '{3}',t.checkpointset = '{4}',t.ncprogramset = '{5}',t.shipstepset = '{6}',
    t.lastchangedate = sysdate,t.lastchangeemp = '{7}',t.allset = '{8}',t.marktype = '2'
WHERE t.id = '{0}'";
                    updateSql = string.Format(updateSql,
                        dr["id"].ToString(),
                        materialset,
                        toolset,
                        auxset,
                        checkpointset,
                        ncprogramset,
                        shipstepset,
                        m_UserInfo.EmployeeID,
                        allset);
                    sqlList.Add(updateSql);
                }

            }
            if (type == "updateworkflow")
            {
                string allset = "0";
                string concat = string.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}",
                                               DBmaterialset,
                                               DBtoolset,
                                               DBauxset,
                                               DBcheckpointset,
                                               DBncprogramset,
                                               DBshipstepset,
                                               resourceset,
                                               qualityset,
                                               workflowset);
                if (concat == "111111111")
                {
                    allset = "1";
                }

                string updateSql = @"
UPDATE ApsPlanInfoPreSpotCheck t 
SET t.resourceset = '{1}',t.qualityset = '{2}',t.workflowset = '{3}',
    t.lastchangedate = sysdate,t.lastchangeemp = '{4}',t.allset = '{5}'
WHERE t.id = '{0}'";
                updateSql = string.Format(updateSql,
                    dr["id"].ToString(),
                    resourceset,
                    qualityset,
                    workflowset,
                    m_UserInfo.EmployeeID,
                    allset);
                sqlList.Add(updateSql);
            }

        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = isSuccess, Message = "修改成功", DBTable = null }));
    }

    //导出
    void exportExcel(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "导出成功" };
        string sql = getQuerySql(context);
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        if (dt.Rows.Count == 0)
        {
            result = new uMESProcessResult() { Result = false, Message = "没有数据不能导出" };
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        else
        {
            foreach (DataRow dr in dt.Rows)
            {
                dr["allset"] = dr["allset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["materialset"] = dr["materialset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["toolset"] = dr["toolset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["auxset"] = dr["auxset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["workflowset"] = dr["workflowset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["ncprogramset"] = dr["ncprogramset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["checkpointset"] = dr["checkpointset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["resourceset"] = dr["resourceset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["shipstepset"] = dr["shipstepset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["qualityset"] = dr["qualityset"].ToString() == "1" ? "齐套" : "预齐套";
                dr["marktype"] = dr["marktype"].ToString() == "1" ? "自动" : "手动";
            }
            dt.Columns.Remove("containerid");
            dt.Columns.Remove("workflowstepid");
            dt.Columns.Remove("type");
            dt.Columns.Remove("status");
            dt.Columns.Remove("apsplandetailpreid");
            dt.Columns.Remove("id");

            dt.Columns["factoryname"].ColumnName = "分厂";
            dt.Columns["mfgordername"].ColumnName = "订单号";
            dt.Columns["productname"].ColumnName = "件号";
            dt.Columns["containername"].ColumnName = "批次号";
            dt.Columns["workflowstepname"].ColumnName = "工序";
            dt.Columns["familylistnames"].ColumnName = "型别";
            dt.Columns["containertype"].ColumnName = "批次类型";
            dt.Columns["workflowinfo"].ColumnName = "工艺";
            dt.Columns["qty"].ColumnName = "在制数";
            dt.Columns["starttime"].ColumnName = "计划开始时间";
            dt.Columns["endtime"].ColumnName = "计划完成时间";

            if (context.Request.Form["ddlCheck"].ToString() == "未点检")
            {
                dt.Columns.Remove("estallsetdate");
                dt.Columns.Remove("allset");
                dt.Columns.Remove("materialset");
                dt.Columns.Remove("toolset");
                dt.Columns.Remove("auxset");
                dt.Columns.Remove("workflowset");
                dt.Columns.Remove("ncprogramset");
                dt.Columns.Remove("checkpointset");
                dt.Columns.Remove("resourceset");
                dt.Columns.Remove("shipstepset");
                dt.Columns.Remove("qualityset");
                dt.Columns.Remove("lastchangeemp");
                dt.Columns.Remove("lastchangedate");
                dt.Columns.Remove("marktype");
            }
            else
            {
                dt.Columns["estallsetdate"].ColumnName = "预齐套时间";
                dt.Columns["allset"].ColumnName = "是否齐套";
                dt.Columns["materialset"].ColumnName = "物料齐套";
                dt.Columns["toolset"].ColumnName = "工装齐套";
                dt.Columns["auxset"].ColumnName = "辅材齐套";
                dt.Columns["workflowset"].ColumnName = "工艺齐套";
                dt.Columns["ncprogramset"].ColumnName = "数控程序齐套";
                dt.Columns["checkpointset"].ColumnName = "特殊过程齐套";
                dt.Columns["resourceset"].ColumnName = "设备齐套";
                dt.Columns["shipstepset"].ColumnName = "实物齐套";
                dt.Columns["qualityset"].ColumnName = "质量齐套";
                dt.Columns["lastchangeemp"].ColumnName = "更新人";
                dt.Columns["lastchangedate"].ColumnName = "更新时间";
                dt.Columns["marktype"].ColumnName = "标记方式";
            }

            string fileName = "预排产工序点检导出" + DateTime.Now.ToString("yyyyMMddHHmmddssffff") + ".xls";
            string strPath = context.Request.MapPath(exportPath + "CheckItemTem/");
            if (!Directory.Exists(strPath)) //如果没有目录，创建
            {
                Directory.CreateDirectory(strPath);
            }
            strPath += fileName;
            byte[] ms = uLMS_CommonFunction.DataTableExportToExcel(dt, null, null);
            uLMS_CommonFunction.ParseBytesToFile(ms, strPath);
            string url = "http://" + context.Request.Url.Host + context.Request.ApplicationPath + exportPath.Trim('~').Replace("\\", "/") + "CheckItemTem/" + fileName;
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("filename", fileName);
            dic.Add("url", url);
            result.ReturnData = dic;
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
    /// <summary>
    /// 一键点检
    /// </summary>
    /// <param name="context"></param>
    void keyspotcheck(HttpContext context) 
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            string sql = getQuerySql(context);
            string ddlType=context.Request["ddlType"].ToString();
            DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
            foreach (DataColumn col in dt.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            uMESProcessResult result = new uMESProcessResult();
            if (dt.Rows.Count > 0)
            {
                result.Result = spotcheckDeal(dt, ddlType);
                result.Message = result.Result ? "一键点检成功" : "一键点检失败";
            }
            else {
                result.Message ="根据条件查询不到需要点检的数据";
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
}